/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package com.elcom.emocbc.dbmapping.persistence;

import com.elcom.emocbc.dbmapping.tables.daos.ProgramBroadcastResultDao;
import com.elcom.emocbc.dbmapping.tables.pojos.ProgramBroadcastResult;
import java.sql.Connection;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.List;
import org.jooq.Configuration;
import org.jooq.ConnectionProvider;
import org.jooq.DSLContext;
import org.jooq.SQLDialect;
import org.jooq.conf.Settings;
import org.jooq.conf.StatementType;
import org.jooq.impl.DSL;
import org.jooq.impl.DefaultConfiguration;

/**
 *
 * @author GiangLT
 */
public class ProgramBroadcastResultPersistance extends ProgramBroadcastResultDao {

    private final com.elcom.emocbc.dbmapping.tables.ProgramBroadcastResult PROGRAM_BROADCAST_RESULT = com.elcom.emocbc.dbmapping.tables.ProgramBroadcastResult.PROGRAM_BROADCAST_RESULT;

    public ProgramBroadcastResultPersistance() throws SQLException {
        ConnectionProvider connectionProvider = new DbConnectionProvider();
        Settings settings = new Settings();
        settings.setStatementType(StatementType.STATIC_STATEMENT);
        Configuration configuration = new DefaultConfiguration()
                .set(connectionProvider)
                .set(SQLDialect.ORACLE)
                .set(settings);
        this.setConfiguration(configuration);
    }

    public List<ProgramBroadcastResult> findInBroadcastTimeRange(Timestamp startTime, Timestamp endTime) throws SQLException {
        Connection con = DbAccess.getInstance().getConnection();
        try {
            DSLContext context = DSL.using(con, SQLDialect.ORACLE);
            return context.selectFrom(PROGRAM_BROADCAST_RESULT)
                    .where(PROGRAM_BROADCAST_RESULT.BROADCAST_TIME.between(startTime, endTime))
                    .orderBy(PROGRAM_BROADCAST_RESULT.BROADCAST_TIME)
                    .fetchInto(ProgramBroadcastResult.class);
        } finally {
            DbAccess.closeConnection(con);
        }
    }
    
    public List<ProgramBroadcastResult> findByZoneInBroadcastTimeRange(Long zoneId, Timestamp startTime, Timestamp endTime) throws SQLException {
        Connection con = DbAccess.getInstance().getConnection();
        try {
            DSLContext context = DSL.using(con, SQLDialect.ORACLE);
            return context.selectFrom(PROGRAM_BROADCAST_RESULT)
                    .where(PROGRAM_BROADCAST_RESULT.BROADCAST_TIME.between(startTime, endTime))
                    .and(PROGRAM_BROADCAST_RESULT.ZONE_ID.equal(zoneId))
                    .orderBy(PROGRAM_BROADCAST_RESULT.BROADCAST_TIME)
                    .fetchInto(ProgramBroadcastResult.class);
        } finally {
            DbAccess.closeConnection(con);
        }
    }
}
